﻿#region

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using BusinessObjects;

#endregion

namespace DataAccessLayer
{
    public static class CityDAL
    {
        private static readonly string ConnectionString =
            ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

        /// <summary>
        ///     Get all cites by district id
        /// </summary>
        /// <param name="districtId">Represent's the district Id</param>
        /// <returns>List of districts</returns>
        public static List<City> GetAllCitiesByDistrictId(byte districtId)
        {
            List<City> cities = new List<City>();
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spGetAllCitiesByDistrictId", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@DistrictId", districtId);

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        City city = new City
                        {
                            CityId = Convert.ToByte(reader["CityId"]),
                            DistrictId = districtId,
                            Name = reader["Name"].ToString()
                        };
                        cities.Add(city);
                    }
                }
            }
            return cities;
        }

        /// <summary>
        ///     Get city by city id
        /// </summary>
        /// <param name="cityId">Represent's the city id</param>
        /// <returns>City</returns>
        public static City GetCityById(byte cityId)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spGetCityById", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@CityId", cityId);

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        City city = new City
                        {
                            CityId = cityId,
                            DistrictId = Convert.ToByte(reader["DistrictId"]),
                            Name = reader["Name"].ToString()
                        };
                        return city;
                    }
                }
            }
            return null;
        }

        public static List<City> GetAllCities()
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spGetAllCities", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    List<City> cities = new List<City>();
                    while (reader.Read())
                    {
                        cities.Add(new City
                        {
                            CityId = Convert.ToByte(reader["CityId"]),
                            Name = reader["Name"].ToString()
                        });
                    }
                    return cities;
                }
            }
        }
    }
}